package com.bj58.groupbuy.action.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.bj58.groupbuy.action.model.IRColumns;
import com.bj58.groupbuy.action.model.IRDrillConfig;
import com.bj58.groupbuy.action.model.IRReport;
import com.bj58.groupbuy.action.model.IRSearchConfig;
import com.bj58.groupbuy.action.service.IRReportService;
import com.bj58.groupbuy.service.locator.ServiceLocator;
import com.bj58.wf.log.Log;
import com.bj58.wf.log.LogFactory;

public class IntSystemDao {
	private IRReportService iRReportService = ServiceLocator.get(IRReportService.class);
	private Log log = LogFactory.getLog(this.getClass());
	
	/**批量插入查询配置及字段配置  (事务)     
	 * @param ircList      预插入的查询条件列表
	 * @param irdList     预插入的钻取信息列表
	 * @param colList     预插入的字段信息列表
	 * @return
	 * @throws Exception
	 */
	public  boolean   saveNewReport(int reportId,List<IRSearchConfig> ircList,List<IRColumns> colList,List<IRDrillConfig> irdList) throws Exception{
		
		Connection con = DBConnectionPool.getConnection(DBConfig.db_68_bi_sys.getIntType());
		Statement st = con.createStatement();
		try {
			con.setAutoCommit(false);  //将自动提交设置为false  
			
			for(IRSearchConfig  vo : ircList ){
				StringBuffer sql = new StringBuffer();
				sql.append("insert into ir_searchconfig (searchTypeId,irReportId,sename,scname,detailValue) values ");
				sql.append("('"+vo.getSearchTypeId()+"',");
				sql.append("'"+reportId+"',");
				sql.append("'"+vo.getSename()+"',");
				sql.append("'"+vo.getScname()+"',");
				sql.append("'"+vo.getDetailValue()+"')");
				st.executeUpdate(sql.toString());
			}
			
			for(IRDrillConfig  vo : irdList ){
				StringBuffer sql = new StringBuffer();
				sql.append("insert into ir_drillconfig (irReportId,drillname,replayname) values ");
				sql.append("('"+reportId+"',");
				sql.append("'"+vo.getDrillname()+"',");
				sql.append("'"+vo.getReplayname()+"')");
				st.executeUpdate(sql.toString());
			}
			for(IRColumns  vo : colList ){
				StringBuffer sql = new StringBuffer();
				sql.append("insert into ir_columns (reportId,ename,cname,tabType,specialInSql,colType,isOrderBy) values ");
				sql.append("('"+reportId+"',");
				sql.append("'"+vo.getEname()+"',");
				sql.append("'"+vo.getCname()+"',");
				sql.append("'"+vo.getTabType()+"',");
				sql.append("'"+vo.getSpecialInSql()+"',");
				sql.append("'"+vo.getColType()+"',");
				sql.append("'"+vo.getIsOrderBy()+"')");
				st.executeUpdate(sql.toString());
			}
			con.commit();      //当三个操作成功后手动提交  
		} catch (SQLException e) {
			con.rollback(); 
			e.printStackTrace();
			return false;
		} finally {
			st.close();
			con.close();
		}
		return true;
	}
	
	
	
	/**批量更新查询配置及字段配置  (事务)     
	 * @param ircList      待更新的查询条件列表
	 * @param colList     待更新的字段信息列表
	 * @return
	 * @throws Exception
	 */
	public  boolean   updateEditedReport(int reportId,List<IRSearchConfig> ircList,List<IRColumns> colList,List<IRDrillConfig> irdList)throws Exception{
		
		Connection con = DBConnectionPool.getConnection(DBConfig.db_68_bi_sys.getIntType());
		Statement st = con.createStatement();
		try {
			con.setAutoCommit(false);  //将自动提交设置为false  
			//删除旧数据
			String deleteSqlOne = "delete from ir_columns  where reportId = "+reportId;
			String deleteSqlTwo = "delete from ir_searchconfig  where irReportId = "+reportId;
			String deleteSqlThree = "delete from ir_drillconfig  where irReportId = "+reportId;
			st.execute(deleteSqlOne);
			st.execute(deleteSqlTwo);
			st.execute(deleteSqlThree);
			//插入新数据
			for(IRSearchConfig  vo : ircList ){
				StringBuffer sql = new StringBuffer();
				sql.append("insert into ir_searchconfig (searchTypeId,irReportId,sename,scname,detailValue) values ");
				sql.append("('"+vo.getSearchTypeId()+"',");
				sql.append("'"+reportId+"',");
				sql.append("'"+vo.getSename()+"',");
				sql.append("'"+vo.getScname()+"',");
				sql.append("'"+vo.getDetailValue()+"')");
				st.executeUpdate(sql.toString());
			}
			for(IRDrillConfig  vo : irdList ){
				StringBuffer sql = new StringBuffer();
				sql.append("insert into ir_drillconfig (irReportId,drillname,replayname) values ");
				sql.append("('"+reportId+"',");
				sql.append("'"+vo.getDrillname()+"',");
				sql.append("'"+vo.getReplayname()+"')");
				st.executeUpdate(sql.toString());
			}
			for(IRColumns  vo : colList ){
				StringBuffer sql = new StringBuffer();
				sql.append("insert into ir_columns (reportId,ename,cname,tabType,specialInSql,colType,isOrderBy) values ");
				sql.append("('"+reportId+"',");
				sql.append("'"+vo.getEname()+"',");
				sql.append("'"+vo.getCname()+"',");
				sql.append("'"+vo.getTabType()+"',");
				sql.append("'"+vo.getSpecialInSql()+"',");
				sql.append("'"+vo.getColType()+"',");
				sql.append("'"+vo.getIsOrderBy()+"')");
				st.executeUpdate(sql.toString());
			}
			con.commit();      //当两个操作成功后手动提交  
		} catch (Exception e) {
			con.rollback(); 
			e.printStackTrace();
			return false;
		}finally {
			st.close();
			con.close();
		}
		return true;
	}
	
	/**通用报表值查询    每行数据组织成字符串返回   value,colType;value,colType...类型（值，字段类型)
	 * @param Sql
	 * @param colTypes
	 * @param type
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public  List<String> irSearchData(String Sql,List<Integer> colTypes,int type,HashMap<String,HashMap<String, Integer>> drillmap) throws Exception {
		List<String> dataList = new ArrayList<String>();
		Connection con = DBConnectionPool.getConnection(type);
		if(null == con){
			for(int i = 0 ; i < 2 ; i++){
				log.info("try again "+i);
				con = DBConnectionPool.getConnection(type);
				if(null != con){
					break;
				}
			}
		}
		Statement st = null;
		ResultSet rs = null;
		String sqlstrtem="";
		PreparedStatement ps = null;
		try {
			log.info("ir  con  is  " + con);
			st = con.createStatement();
		for(int k=0;k<10;k++)
		{   
		   if(!Sql.contains(" limit "))
		   {
			sqlstrtem=Sql+" LIMIT "+String.valueOf(k*500000)+",500000";
		   }else {
			   sqlstrtem=Sql;
		   }
			/*System.out.println(sqlstrtem);*/
			st = con.createStatement();
			ps = (PreparedStatement) con.prepareStatement(sqlstrtem,ResultSet.TYPE_FORWARD_ONLY,
		              ResultSet.CONCUR_READ_ONLY);
			 //ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE
			ps.setFetchSize(Integer.MIN_VALUE);
			ps.setFetchDirection(ResultSet.FETCH_REVERSE);
			rs = ps.executeQuery();
			int colType=0;
			boolean flag=false;
			String  value="";
		/*	int count=0;*/
			StringBuffer result  = new StringBuffer();	
			int i=0;
			while(rs.next()){
					
				result.setLength(0);
				/*count++;
				if(count%100000==0)
				{
					log.info("执行行数："+count);
				}*/
				for(i = 0; i< colTypes.size(); i++){
				    colType = colTypes.get(i);
				    flag=false;
				    value =String.valueOf(rs.getObject(i+1));
					if(null == value || "".equals(value) || "null".equals(value)){
						value = "--";
					}
					value = value.replace(",", "，");
					result.append(value+"," + colType + "," + value);
					//如果group中包含下一级的钻取字段，则不允许本字段钻取
					//添加钻取信息
					if(drillmap!=null&&drillmap.size()>0)//drillmap类型：
					{
						Iterator iter = drillmap.entrySet().iterator();
						String drillcols="";
						while(iter.hasNext()) {
					        Map.Entry entry = (Map.Entry) iter.next();
						    String key = entry.getKey().toString();
						    HashMap<String, Integer> val =(HashMap<String, Integer>) entry.getValue();//其中的integer类型存储在结果集中 的位置
						    if(val.get(key)==i)//确定该字段是否需要钻取，需要钻取时其列数一致则需要钻取，比如get(key)=3,即只有第四列需要钻取，添加相应的信息
						    {
							     drillcols="key:"+key+"#groupBy:";
							     Iterator iterlevel1 = val.entrySet().iterator();
						    	 while(iterlevel1.hasNext()) {
							    	  Map.Entry entrylevel1 = (Map.Entry) iterlevel1.next();
							    	  String keylevel1 = entrylevel1.getKey().toString();
							          drillcols +=keylevel1+"='"+rs.getObject(Integer.parseInt(entrylevel1.getValue().toString())+1)+"'="+entrylevel1.getValue().toString()+" and ";	
						    	 }
						    	 drillcols +="#sql:"+Sql;
						    	 drillcols +="#colTypes:"+colTypes.toString();
						    	 
						    	 if(!val.containsKey("flag"))
						    	 {
						    		 result.append(","+drillcols);
						    		 flag=true;
						    	 }
						    }
						 }			
					}
					if(flag==false)
					{
					  result.append(",no");
					}
					if((i+1) < colTypes.size()){
						
						result.append(";");
					}
				}
				dataList.add(result.toString());
			}
			if(dataList.size()<(k+1)*500000)
			  {
			/*	System.out.println("maxsize is:"+dataList.size());*/
				break;
			   }
		  }
		
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUitl.closeResultSet(rs); JdbcUitl.closeStatement(st); JdbcUitl.closeConnection(con);
		}
		/*System.out.println("CCCCCCCC...");*/
		return dataList;
	}
	
	/**
	 * 邮件下载专用查询至execl
	 * *//*
	public  List<String> irSearchDataForMail(String Sql,List<Integer> colTypes,int type,HashMap<String,HashMap<String, 

Integer>> drillmap) throws Exception {
		List<String> dataList = new ArrayList<String>();
		String sqlstrtem="";
		for(int k=0;k<10;k++)
		{   
		   if(!Sql.contains(" limit "))
		   {
			sqlstrtem=Sql+" LIMIT "+String.valueOf(k*500000)+",500000";
		   }else {
			   sqlstrtem=Sql;
		   }
			System.out.println(sqlstrtem);
			Connection con = DBConnectionPool.getConnection(type);
			if(null == con){
				for(int i = 0 ; i < 2 ; i++){
					log.info("try again "+i);
					con = DBConnectionPool.getConnection(type);
					if(null != con){
						break;
					}
				}
			}
		    log.info("ir  con  is  " + con);
		
		
		 Statement st = null;
		 ResultSet rs = null;
		 PreparedStatement ps = null;
		 try {
			
			st = con.createStatement();
		
			ps = (PreparedStatement) con.prepareStatement(sqlstrtem,ResultSet.TYPE_FORWARD_ONLY,
		              ResultSet.CONCUR_READ_ONLY);
			 //ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE
			ps.setFetchSize(Integer.MIN_VALUE);
			ps.setFetchDirection(ResultSet.FETCH_REVERSE);
           
			rs = ps.executeQuery();
			int colType=0;
			boolean flag=false;
			String  value="";
			int count=0;
			System.out.println("rs next begin");
			StringBuffer result  = new StringBuffer();	
			int i=0;
			while(rs.next()){
				result.setLength(0);
				count++;
				if(count%100000==0)
				{
					log.info("执行行数："+count);
				}
			  for( i = 0; i< colTypes.size(); i++){
				    colType = colTypes.get(i);
				    flag=false;
					value =String.valueOf(rs.getObject(i+1));
					if(null == value || "".equals(value) || "null".equals(value)){
						value = "--";
					}
					value = value.replace(",", "，");
					result.append(value+"," + colType + "," + value);
					//如果group中包含下一级的钻取字段，则不允许本字段钻取
					//添加钻取信息
					if(drillmap!=null&&drillmap.size()>0)//drillmap类型：
					{
						Iterator iter = drillmap.entrySet().iterator();
						String drillcols="";
						while(iter.hasNext()) {
					        Map.Entry entry = (Map.Entry) iter.next();
						    String key = entry.getKey().toString();
						    HashMap<String, Integer> val =(HashMap<String, Integer>) entry.getValue();//其中的integer类型存储在结果集中 的位置
						    if(val.get(key)==i)//确定该字段是否需要钻取，需要钻取时其列数一致则需要钻取，比如get(key)=3,即只有第四列需要钻取，添加相应的信息
						    {
							     drillcols="key:"+key+"#groupBy:";
							     Iterator iterlevel1 = val.entrySet().iterator();
						    	 while(iterlevel1.hasNext()) {
							    	  Map.Entry entrylevel1 = (Map.Entry) iterlevel1.next();
							    	  String keylevel1 = entrylevel1.getKey().toString();
							          drillcols +=keylevel1+"='"+rs.getObject(Integer.parseInt(entrylevel1.getValue().toString())+1)+"'="+entrylevel1.getValue().toString()+" and ";	
						    	 }
						    	 drillcols +="#sql:"+Sql;
						    	 drillcols +="#colTypes:"+colTypes.toString();
						    	 
						    	 if(!val.containsKey("flag"))
						    	 {
						    		 result.append(","+drillcols);
						    		 flag=true;
						    	 }
						    }
						 }			
					}
					if(flag==false)
					{
					  result.append(",no");
					}
					if((i+1) < colTypes.size()){
						
						result.append(";");
					}
				}
				dataList.add(result.toString());
			
			   }
			  if(dataList.size()<(k+1)*500000)
			  {
				System.out.println("maxsize is:"+dataList.size());
				break;
			   }
			  } catch (SQLException e) {
				e.printStackTrace();
			 } finally {
				JdbcUitl.closeResultSet(rs); JdbcUitl.closeStatement(st); JdbcUitl.closeConnection(con);
			}
		}
		System.out.println("CCCCCCCC...");
		return dataList;
	}
	
	
	*/
	
	/**修改报表状态
	 * @param reportId
	 * @param state
	 * @return
	 * @throws Exception
	 */
	public  boolean   updateReportState(int reportId,int state) throws Exception{
		
		Connection con = DBConnectionPool.getConnection(DBConfig.db_68_bi_sys.getIntType());
		Statement st = con.createStatement();
		try {
			IRReport   report = iRReportService.getById(reportId);
			int menuId = report.getMenuId();
			
			String reportSql = "update ir_report   set  state = "+state+" where id = "+reportId;
			String menuSql = "update menu   set  isshow = "+state+" where id = "+menuId;
			
			String subMailSql = "";//报表下线 对应的邮件订阅也取消
			if(state == 0 ){
				subMailSql = "update ir_subemail set state = 2 where reportId = " + reportId;
			}else if(state == 1 ){
				subMailSql = "update ir_subemail set state = 1 where reportId = " + reportId;
			}
			
			con.setAutoCommit(false);  //将自动提交设置为false  
			st.executeUpdate(reportSql);
			st.executeUpdate(menuSql);
			st.executeUpdate(subMailSql);
			con.commit();      //提交  
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			return false;
		} finally {
			st.close();
			con.close();
		}
		return true;
	}
	
	
	
	/**根据mainId删除自定义邮件的相关数据
	 */
	public boolean deleteIEmailByMainId(int mainId) throws SQLException{
		
		Connection con = DBConnectionPool.getConnection(DBConfig.db_68_bi_sys.getIntType());
		Statement st = con.createStatement();
		try {
			String sql1 = "delete from ie_main  where id = " + mainId ;
			String sql2 = "delete from ie_send  where mainId = " + mainId ;
			String sql3 = "delete from ie_table  where mainId = " + mainId ;
			String sql4 = "delete from ie_tablesearch  where mainId = " + mainId ;
			
			con.setAutoCommit(false);  //将自动提交设置为false  
			st.execute(sql1);
			st.execute(sql2);
			st.execute(sql3);
			st.execute(sql4);
			
			con.commit();         //提交  
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			return false;
		} finally {
			st.close();
			con.close();
		}
		return true;
	}
	
}
